﻿namespace Snipps.Model
{
    using System;
    using System.Collections.Generic;
    using System.Data.SQLite;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using Dapper;
    using Snipps.Core;
    using Snipps.Core.Entities;

    public class SnippetsDataContext : ISnippetsDataContext
    {
        private readonly string _fileConnectionString;

        private readonly SQLiteConnection _memoryDB;

        private readonly ISettings _settings;

        private readonly string[] _tablesNames = new[] { "ProgrammingLangs", "Tags", "Snippets", "SnippetsTags" };

        private readonly string _tempDbFilePath;

        private readonly string _tempFileConnectionString;

        private bool _disposed;

        private bool _dbLoaded;

        private bool _isContextChanged;

        public SnippetsDataContext(ISettings settings)
        {
            _settings = settings;
            var tempDir = Path.GetTempPath();
            _tempDbFilePath = Path.Combine(tempDir, "snippets.db");
            _tempFileConnectionString = "Data Source=" + _tempDbFilePath;

            var fileDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            var fileDbPath = Path.Combine(fileDir, "snippets.db");
            _fileConnectionString = "Data Source=" + fileDbPath;
            _memoryDB = new SQLiteConnection("Data Source=:memory:");
            _memoryDB.Open();
        }

        public void SaveChanges()
        {
            if (_isContextChanged)
            {
                WriteSnippetsToDbFile();
                _isContextChanged = false;
            }
        }

        public IList<Snippet> FilterSnippets(string snippetName, ProgrammingLang programmingLang = null, IList<Tag> tags = null)
        {
            CheckIfDbLoaded();

            var pattern = GetFilterPattern(snippetName);
            Func<Snippet, ProgrammingLang, Snippet> addSyncLangAndTagsToSnippetObject = (snippet, lang) =>
                {
                    snippet.ProgrammingLang = lang;
                    var snippetTags =
                        _memoryDB.Query<Tag>(
                            @"select * from Tags INNER JOIN SnippetsTags ON Tags.Id = SnippetsTags.TagId WHERE SnippetsTags.SnippetId = @SnippetId", 
                            new { SnippetId = snippet.Id }).ToList();
                    if (snippetTags.Count > 0)
                    {
                        snippet.Tags = snippetTags;
                    }

                    return snippet;
                };
            var queryParameters = QueryParametersForGetSnippets(pattern, programmingLang, tags, 0, 700);
            var sqlQuery = SqlQueryForGetSnippets(programmingLang, tags, "LIKE");

            IList<Snippet> ret =
                _memoryDB.Query(sqlQuery, addSyncLangAndTagsToSnippetObject, queryParameters, splitOn: "Id", buffered: false)
                         .ToList();
            return ret;
        }

        public IList<Tag> FilterTags(string tagName, IList<Tag> excludedTags)
        {
            CheckIfDbLoaded();

            const int pageSize = 700;
            const int pageIndex = 0;
            var filterPattern = GetFilterPattern(tagName);
            var sqlQuery = @"select * from Tags WHERE Name LIKE @FilterText
                      ORDER BY Tags.Name LIMIT @PageSize OFFSET @Offset;";

            var sqlParameters =
                new
                    {
                        ExcludeTags = excludedTags.Select(x => x.Id).ToArray(), 
                        FilterText = filterPattern, 
                        PageSize = pageSize, 
                        Offset = pageIndex * pageSize, 
                    };

            if (excludedTags.Count > 0)
            {
                sqlQuery = @"select * from Tags WHERE Name LIKE @FilterText
                        AND Id NOT IN @ExcludeTags
                      ORDER BY Tags.Name LIMIT @PageSize OFFSET @Offset;";
            }

            return _memoryDB.Query<Tag>(sqlQuery, sqlParameters).ToList();
        }

        public IList<ProgrammingLang> FilterProgrammingLangs(string filterText)
        {
            CheckIfDbLoaded();

            const int pageSize = 100;
            const int pageIndex = 0;
            var filterPattern = GetFilterPattern(filterText);
            const string sqlQuery = @"select * from ProgrammingLangs WHERE Name LIKE @FilterText
                      ORDER BY ProgrammingLangs.Name LIMIT @PageSize OFFSET @Offset;";
            var sqlParameters = new { FilterText = filterPattern, PageSize = pageSize, Offset = pageIndex * pageSize, };
            return _memoryDB.Query<ProgrammingLang>(sqlQuery, sqlParameters).ToList();
        }

        public Snippet GetSnippet(string snippetName, ProgrammingLang programmingLang)
        {
            CheckIfDbLoaded();

            Func<Snippet, ProgrammingLang, Snippet> addSyncLangAndTagsToSnippetObject = (snippet, lang) =>
                {
                    snippet.ProgrammingLang = lang;
                    var snippetTags =
                        _memoryDB.Query<Tag>(
                            @"select * from Tags INNER JOIN SnippetsTags ON Tags.Id = SnippetsTags.TagId WHERE SnippetsTags.SnippetId = @SnippetId", 
                            new { SnippetId = snippet.Id }).ToList();
                    if (snippetTags.Count > 0)
                    {
                        snippet.Tags = snippetTags;
                    }

                    return snippet;
                };
            var queryParameters = QueryParametersForGetSnippets(snippetName, programmingLang, null, 0, 100);
            var sqlQuery = SqlQueryForGetSnippets(programmingLang, null);

            return _memoryDB.Query(sqlQuery, addSyncLangAndTagsToSnippetObject, queryParameters, buffered: false).SingleOrDefault();
        }

        public Tag GetTag(string tagName)
        {
            CheckIfDbLoaded();

            const string sql = @"select * from Tags WHERE Name = @TagName";

            return _memoryDB.Query<Tag>(sql, new { TagName = tagName }).SingleOrDefault();
        }

        public void RemoveTag(Tag tag)
        {
            CheckIfDbLoaded();

            const string sqlQuery = @"Delete from SnippetsTags Where TagId = @Id;
                                Delete from Tags Where Id = @Id;";
            using (var transaction = _memoryDB.BeginTransaction())
            {
                _memoryDB.Execute(sqlQuery, tag);
                transaction.Commit();
                _isContextChanged = true;
            }
        }

        public void RemoveSnippet(Snippet snippet)
        {
            CheckIfDbLoaded();

            const string sqlQuery = @"Delete from SnippetsTags Where SnippetId = @Id;
                                Delete from Snippets Where Id = @Id;";
            using (var transaction = _memoryDB.BeginTransaction())
            {
                _memoryDB.Execute(sqlQuery, snippet);
                transaction.Commit();
                _isContextChanged = true;
            }
        }

        public void SaveTag(Tag tag)
        {
            CheckIfDbLoaded();

            var sqlQuery = @"insert into Tags values(null,@Name); select last_insert_rowid();";
            if (!tag.IsNew)
            {
                sqlQuery = @"update Tags SET Name = @Name Where Id = @Id;";
            }

            using (var transaction = _memoryDB.BeginTransaction())
            {
                if (!tag.IsNew)
                {
                    _memoryDB.Execute(sqlQuery, tag);
                }
                else
                {
                    tag.Id = _memoryDB.Query<long>(sqlQuery, tag).Single();
                }

                transaction.Commit();
                _isContextChanged = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        public void SaveSnippet(Snippet snippet)
        {
            CheckIfDbLoaded();

            var sqlQuery = @"insert into Snippets values(null,@Name,@Content,@ProgrammingLangId); select last_insert_rowid();";
            if (!snippet.IsNew)
            {
                sqlQuery = @"update Snippets SET Name = @Name, Content = @Content,ProgrammingLangId = @ProgrammingLangId Where Id = @Id;";
            }

            long? slangid = null;
            if (snippet.ProgrammingLang != null)
            {
                slangid = snippet.ProgrammingLang.Id;
            }

            var sqlParameters = new { snippet.Id, snippet.Name, snippet.Content, ProgrammingLangId = slangid };

            using (var transaction = _memoryDB.BeginTransaction())
            {
                if (!snippet.IsNew)
                {
                    _memoryDB.Execute(sqlQuery, sqlParameters);
                }
                else
                {
                    snippet.Id = _memoryDB.Query<long>(sqlQuery, sqlParameters).Single();
                }

                _memoryDB.Execute(@"Delete from SnippetsTags Where SnippetId = @SnippetId", new { SnippetId = snippet.Id });
                if (snippet.Tags.Any())
                {
                    _memoryDB.Execute(
                        @"insert into SnippetsTags values(@TagId,@SnippetId);", 
                        snippet.Tags.Select(x => new { TagId = x.Id, SnippetId = snippet.Id }));
                }

                transaction.Commit();
                _isContextChanged = true;
            }
        }

        private void ReadSnippetsFromDbFile()
        {
            var fileConnectionString = _fileConnectionString;
            if (_settings.TempFileIsUsed)
            {
                fileConnectionString = _tempFileConnectionString;
            }

            var fileDB = new SQLiteConnection(fileConnectionString);
            var ok = true;
            try
            {
                fileDB.Open();
                fileDB.BackupDatabase(_memoryDB, "main", "main", -1, null, 0);
            }
            catch (SQLiteException)
            {
                ok = false;
            }
            finally
            {
                fileDB.Dispose();
            }

            if (!ok || !DbIsValid(_memoryDB))
            {
                CreateDbSchema(_memoryDB);
                //CreateSampleDB(_memoryDB);

                WriteSnippetsToDbFile();
            }

            _dbLoaded = true;
        }

	private void CheckIfDbLoaded()
	{
	    if (!_dbLoaded)
	    {
	        ReadSnippetsFromDbFile();
	    }	
	}

        private bool DbIsValid(SQLiteConnection db)
        {
            const string sql = @"SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;";

            var returnedTablesNames = db.Query<string>(sql).ToList();

            foreach (var tablesName in _tablesNames)
            {
                if (!returnedTablesNames.Contains(tablesName))
                {
                    return false;
                }
            }

            return true;
        }

        private void WriteSnippetsToDbFile()
        {
            var fileDB = new SQLiteConnection(_fileConnectionString);
            try
            {
                fileDB.Open();
                _memoryDB.BackupDatabase(fileDB, "main", "main", -1, null, 0);
                _settings.TempFileIsUsed = false;
            }
            catch (SQLiteException)
            {
                // TODO: notify user that temp path will be used for saving
                fileDB = new SQLiteConnection(_tempFileConnectionString);
                fileDB.Open();
                _memoryDB.BackupDatabase(fileDB, "main", "main", -1, null, 0);
                _settings.TempFileIsUsed = true;
            }
            finally
            {
                fileDB.Dispose();
            }
        }

        private string GetFilterPattern(string filterText)
        {
            var pattern = string.Join("%", filterText.AsEnumerable());
            pattern += "%";
            pattern = pattern.Insert(0, "%");
            return pattern;
        }

        private object QueryParametersForGetSnippets(
            string filterText, ProgrammingLang programmingLang, IList<Tag> tags, int pageIndex, int pageSize)
        {
            var programmingLangName = string.Empty;
            if (programmingLang != null)
            {
                programmingLangName = programmingLang.Name;
            }

            var tagIds = new long[] { };
            var tagsCount = 0;
            if (tags != null && tags.Any())
            {
                tagsCount = tags.Count;
                tagIds = tags.Select(t => t.Id).ToArray();
            }

            return
                new
                    {
                        FilterText = filterText, 
                        PageSize = pageSize, 
                        Offset = pageIndex * pageSize, 
                        ProgrammingLangName = programmingLangName, 
                        TagsCount = tagsCount, 
                        TagIDs = tagIds
                    };
        }

        private string SqlQueryForGetSnippets(ProgrammingLang programmingLang, IEnumerable<Tag> tags, string snippetCompareOperator = "=")
        {
            var joinSql = " LEFT JOIN ProgrammingLangs ON Snippets.ProgrammingLangId = ProgrammingLangs.Id";
            var whereSql = " WHERE Snippets.Name " + snippetCompareOperator + " @FilterText";
            var having = string.Empty;

            if (programmingLang != null)
            {
                whereSql += " AND ProgrammingLangs.Name = @ProgrammingLangName";
            }

            if (tags != null && tags.Any())
            {
                having =
                    @" HAVING @TagsCount <= (select Count(st2.TagId) from Snippets s2 LEFT JOIN SnippetsTags st2 ON st2.SnippetId = s2.Id WHERE s2.Id = Snippets.Id AND st2.TagId IN @TagIDs)";
                joinSql +=
                    @" LEFT JOIN SnippetsTags ON SnippetsTags.SnippetId = Snippets.Id LEFT JOIN Tags ON SnippetsTags.TagId = Tags.Id";
            }

            var sqlQuery = @"select * from Snippets" + joinSql + whereSql + @" Group By Snippets.Id" + having
                           + " ORDER BY Snippets.Name LIMIT @PageSize OFFSET @Offset;";

            return sqlQuery;
        }

        private void CreateDbSchema(SQLiteConnection db)
        {
            const string createTables = @"
CREATE TABLE ProgrammingLangs
(
Id INTEGER PRIMARY KEY,
Name TEXT collate nocase
);
CREATE TABLE Tags
(
Id INTEGER PRIMARY KEY,
Name TEXT collate nocase
);
CREATE TABLE Snippets
(
Id INTEGER PRIMARY KEY,
Name TEXT collate nocase,
Content TEXT,
ProgrammingLangId INTEGER,
FOREIGN KEY(ProgrammingLangId) REFERENCES ProgrammingLang(Id)
);
CREATE TABLE SnippetsTags
(
TagId INTEGER NOT NULL,
SnippetId INTEGER NOT NULL,
FOREIGN KEY(TagId) REFERENCES Tag(Id),
FOREIGN KEY(SnippetId) REFERENCES Snippet(Id),
PRIMARY KEY (SnippetId, TagId)
);
CREATE INDEX idx_sin ON Snippets(Id,Name collate nocase);
CREATE INDEX idx_tin ON Tags(Id,Name collate nocase);
CREATE INDEX idx_stsiti ON SnippetsTags(SnippetId,TagId);

";

            var programmingLangs = SampleData.GetAllProgrammingLangs();
            using (var transaction = db.BeginTransaction())
            {
                db.Execute(createTables, transaction: transaction);
                db.Execute("insert into ProgrammingLangs values(@Id, @Name);", programmingLangs, transaction);
                transaction.Commit();
            }
        }

        private void AddFakeDataToDb(SQLiteConnection db)
        {
            var snippets = SampleData.GenerateRandomSnippets();
            var tags = SampleData.GenerateFakeTags();

            using (var transaction = db.BeginTransaction())
            {
                db.Execute("insert into Tags values(@Id, @Name);", tags, transaction);
                var isnipps = from s in snippets select new { s.Id, s.Name, s.Content, ProgrammingLangId = s.ProgrammingLang.Id };
                var issfsf = isnipps.ToArray();
                db.Execute("insert into Snippets values(@Id, @Name,@Content,@ProgrammingLangId);", issfsf, transaction);
                var snippsTags = from s in snippets from t in s.Tags select new { TagId = t.Id, SnippetId = s.Id };

                db.Execute("insert into SnippetsTags values(@TagId,@SnippetId);", snippsTags.ToArray(), transaction);

                transaction.Commit();
            }
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _memoryDB.Dispose();
                }

                _disposed = true;
            }
        }
    }
}